﻿/*
Foundation 的部署脚本
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "Foundation"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

GO
:on error exit
GO
USE [master]
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'目标数据库 %s 的状态未设置为 ONLINE。要部署此数据库，数据库状态必须设置为 ONLINE。', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO

IF NOT EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)')
BEGIN
    RAISERROR(N'不能将此更新脚本部署到目标 COOLMAN-PC。此服务器上没有为此脚本生成的数据库 Foundation。', 16, 127) WITH NOWAIT
    RETURN
END

GO

IF (@@servername != 'COOLMAN-PC')
BEGIN
    RAISERROR(N'生成脚本中的服务器名称 %s 与目标服务器的名称 %s 不匹配。请验证数据库项目设置是否正确以及生成脚本是否是最新的。', 16, 127,N'COOLMAN-PC',@@servername) WITH NOWAIT
    RETURN
END

GO

IF CAST(DATABASEPROPERTY(N'$(DatabaseName)','IsReadOnly') as bit) = 1
BEGIN
    RAISERROR(N'无法部署此更新脚本，因为之前生成脚本时选择的目标数据库 %s 现已设置为 READ_ONLY。', 16, 127, N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
USE [$(DatabaseName)]
GO
/*
 预先部署脚本模板							
--------------------------------------------------------------------------------------
 此文件包含将在生成脚本前执行的 SQL 语句	
 使用 SQLCMD 语法将文件包含在预先部署脚本中			
 示例:      :r .\filename.sql								
 使用 SQLCMD 语法引用预先部署脚本中的变量		
 示例:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/




GO
/*
-- 后期部署脚本模板							
----------------------------------------------------------------------------------------
-- 此文件包含将追加到生成脚本的 SQL 语句		
-- 使用 SQLCMD 语法将文件包含到后期部署脚本中			
-- 示例:      :r .\filename.sql								
-- 使用 SQLCMD 语法引用后期部署脚本中的变量		
-- 示例:      :setvar $TableName MyTable							
--               SELECT * FROM [$(TableName)]					
----------------------------------------------------------------------------------------

*/

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRolesView]'))
DROP VIEW [dbo].[UsersInRolesView]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoles]') AND type in (N'U'))
DROP TABLE [dbo].[UsersInRoles]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RegisterUser]') AND type in (N'U'))
DROP TABLE [dbo].[RegisterUser]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Role]') AND type in (N'U'))
DROP TABLE [dbo].[Role]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoleCategory]') AND type in (N'U'))
DROP TABLE [dbo].[RoleCategory]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
DROP TABLE [dbo].[User]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountContext]') AND type in (N'U'))
DROP TABLE [dbo].[AccountContext]
GO

CREATE TABLE [dbo].[AccountContext]
(
	[AccountContextCode] [nvarchar] (16) NOT NULL
		constraint PK_AccountContext primary key,
	[EnablePasswordRetrieval] [bit] NOT NULL,
	[EnablePasswordReset] [bit] NOT NULL,
	[RequiresQuestionAndAnswer] [bit] NOT NULL,
	[RequiresUniqueEmail] [bit] NOT NULL,
	[PasswordFormat] [nvarchar] (64) NOT NULL,
	[MaxInvalidPasswordAttempts] [int] NOT NULL,
	[PasswordAttemptWindow] [int] NOT NULL,
	[MinRequiredPasswordLength] [int] NOT NULL,
	[MinRequiredPasswordSymbols] [int] NOT NULL,
	[PasswordStrengthRegularExpression] [nvarchar] (128) NULL,
	[Title] [nvarchar] (64) NOT NULL,
	[Description] [nvarchar] (256) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[RoleCategory]
(
	[RoleCategoryId] [int] identity(1,1) NOT NULL
		constraint PK_RoleCategory primary key,
	[Title] [nvarchar](64) NOT NULL,
	[Description] [nvarchar](256) NOT NULL,
	[AccountContextCode] [nvarchar](16) NOT NULL
		constraint FK_RoleCategory_AccountContext foreign key references AccountContext(AccountContextCode),
	constraint UIX_AccountContextCode_Tile unique (AccountContextCode, Title)
) ON [PRIMARY]

CREATE TABLE [dbo].[Role]
(
	[RoleId] int identity(1,1) NOT NULL
		constraint PK_Role primary key,
	[RoleCategoryId] int NOT NULL
		constraint FK_Role_RoleCategory foreign key references RoleCategory( RoleCategoryId ),
	[RoleName] [nvarchar] (64) NOT NULL,
	[LoweredRoleName] [nvarchar] (64) NOT NULL,
	[Description] [nvarchar] (256) NULL,
	constraint UIX_RoleCategoryId_LoweredRoleName unique (RoleCategoryId, LoweredRoleName )
) ON [PRIMARY]

CREATE TABLE [dbo].[User]
(
	[AccountContextCode] [nvarchar](16) NOT NULL
		constraint FK_User_AccountContext foreign key references AccountContext ( AccountContextCode ),
	[UserGuid] [uniqueidentifier] NOT NULL
		constraint PK_User primary key,
	[IsAnonymous] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[RegisterUser]
(
	[AccountContextCode] [nvarchar] (16) NOT NULL
		constraint FK_RegisterUser_AccountContext foreign key references AccountContext ( AccountContextCode ),
	[UserGuid] [uniqueidentifier] NOT NULL
		constraint PK_RegisterUser primary key
		constraint FK_RegisterUser_User foreign key references [User] (UserGuid),
	[UserName] [nvarchar] (64) NOT NULL,
	[LoweredUserName] [nvarchar] (64) NOT NULL,
	[NameAlias] [nvarchar] (64) NULL,
	[MobileAlias] [nvarchar] (64) NULL,
	[Password] [nvarchar] (128) NOT NULL,
	[PasswordFormat] [int] NOT NULL,
	[PasswordSalt] [nvarchar] (128) NOT NULL,
	[MobilePin] [nvarchar] (64) NULL,
	[Email] [nvarchar] (128) NULL,
	[LoweredEmail] [nvarchar] (128) NULL,
	[PasswordQuestion] [nvarchar] (128) NULL,
	[PasswordAnswer] [nvarchar] (128) NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[LastLoginDate] [datetime] NOT NULL,
	[LastPasswordChangedDate] [datetime] NOT NULL,
	[LastLockedOutDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
	[Comment] [nvarchar] (512) NULL,
	constraint UIX_AccountContextCode_LoweredEmail unique (AccountContextCode,LoweredEmail),
	constraint UIX_AccountContextCode_LoweredUserName unique (AccountContextCode,LoweredUserName)
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersInRoles]
(
	[UserGuid] [uniqueidentifier] NOT NULL
		constraint FK_UsersInRoles_RegisterUser foreign key references RegisterUser (UserGuid),
	[RoleId] int NOT NULL
		constraint FK_UsersInRoles_Role foreign key references [Role] (RoleId),
	constraint PK_UsersInRoles primary key (UserGuid, RoleId)
) ON [PRIMARY]


GO

CREATE VIEW dbo.UsersInRolesView
AS
SELECT     dbo.Role.RoleName, dbo.Role.LoweredRoleName, dbo.Role.RoleCategoryId, dbo.UsersInRoles.RoleId, dbo.UsersInRoles.UserGuid, 
                      dbo.RegisterUser.AccountContextCode, dbo.RegisterUser.LoweredUserName, dbo.RegisterUser.NameAlias, dbo.RegisterUser.LoweredEmail, 
                      dbo.RegisterUser.UserName, dbo.RegisterUser.Email, dbo.RoleCategory.Title, dbo.RoleCategory.[Description]
FROM         dbo.Role INNER JOIN
                      dbo.RoleCategory ON dbo.Role.RoleCategoryId = dbo.RoleCategory.RoleCategoryId INNER JOIN
                      dbo.UsersInRoles ON dbo.Role.RoleId = dbo.UsersInRoles.RoleId INNER JOIN
                      dbo.RegisterUser ON dbo.UsersInRoles.UserGuid = dbo.RegisterUser.UserGuid
GO

DELETE FROM  [UsersInRoles] 
 
DELETE FROM  [RegisterUser] 
 
DELETE FROM  [User] 

DELETE FROM  [Role]

DELETE FROM  [RoleCategory]

DELETE FROM  [AccountContext]
 
-- AccountContext
INSERT INTO [AccountContext] ([AccountContextCode] ,[EnablePasswordRetrieval] ,[EnablePasswordReset] ,[RequiresQuestionAndAnswer] ,[RequiresUniqueEmail] ,[PasswordFormat] ,[MaxInvalidPasswordAttempts] ,[PasswordAttemptWindow] ,[MinRequiredPasswordLength] ,[MinRequiredPasswordSymbols] ,[PasswordStrengthRegularExpression] ,[Title] ,[Description] ) VALUES ( 'Public',1,1,1,1,'Clear',5,5,6,0,Null,'���ڷ�����վ���˺�',Null )
INSERT INTO [AccountContext] ([AccountContextCode] ,[EnablePasswordRetrieval] ,[EnablePasswordReset] ,[RequiresQuestionAndAnswer] ,[RequiresUniqueEmail] ,[PasswordFormat] ,[MaxInvalidPasswordAttempts] ,[PasswordAttemptWindow] ,[MinRequiredPasswordLength] ,[MinRequiredPasswordSymbols] ,[PasswordStrengthRegularExpression] ,[Title] ,[Description] ) VALUES ( 'Internal',1,1,1,1,'Clear',5,5,6,0,Null,'�ڲ�������վ���˺�',Null )

-- RoleCategory
SET IDENTITY_INSERT [RoleCategory] ON 
INSERT INTO [RoleCategory] ([RoleCategoryId] ,[Title] ,[Description] ,[AccountContextCode] ) VALUES ( 1,'Common','ͨ�ý�ɫ���','Public' )
INSERT INTO [RoleCategory] ([RoleCategoryId] ,[Title] ,[Description] ,[AccountContextCode] ) VALUES ( 2,'Common','ͨ�ý�ɫ���','Internal' )
SET IDENTITY_INSERT [RoleCategory] OFF 

-- Role
SET IDENTITY_INSERT [Role] ON
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 1,1,'User','user','��ͨ�û�' )
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 2,1,'Admin','admin','��վ����Ա' )
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 3,2,'User','user','��ͨ�û�' )
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 4,2,'Admin','admin','��վ����Ա' )
SET IDENTITY_INSERT [Role] OFF

-- User
-- for Public - Common - User
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )
-- for Public - Common - Admin
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )
-- for Internal - Common - User
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )
-- for Internal - Common - Admin
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )

-- RegisterUser
-- for Public - Common - User
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','user5','user5','','','user5',0,'s','','user5@email.com','user5@email.com','user5','user5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1ac712c6-1ee3-4053-99b5-37949b3054c3','user4','user4','','','user4',0,'s','','user4@email.com','user4@email.com','user4','user4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1964ec47-2ac7-4c33-9660-523fa9ebb799','user3','user3','','','user3',0,'s','','user3@email.com','user3@email.com','user3','user3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1c84bf0a-b4f9-4059-99c0-669f649efefe','user1','user1','','','user1',0,'s','','user1@email.com','user1@email.com','user1','user1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1b972aa7-04f9-4db9-90da-e0a878a5fc7a','user2','user2','','','user2',0,'s','','user2@email.com','user2@email.com','user2','user2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )
-- for Public - Common - Admin
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','admin5','admin5','','','admin5',0,'s','','admin5@email.com','admin5@email.com','admin5','admin5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2ac712c6-1ee3-4053-99b5-37949b3054c3','admin4','admin4','','','admin4',0,'s','','admin4@email.com','admin4@email.com','admin4','admin4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2964ec47-2ac7-4c33-9660-523fa9ebb799','admin3','admin3','','','admin3',0,'s','','admin3@email.com','admin3@email.com','admin3','admin3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2c84bf0a-b4f9-4059-99c0-669f649efefe','admin1','admin1','','','admin1',0,'s','','admin1@email.com','admin1@email.com','admin1','admin1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2b972aa7-04f9-4db9-90da-e0a878a5fc7a','admin2','admin2','','','admin2',0,'s','','admin2@email.com','admin2@email.com','admin2','admin2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )
-- for Internal - Common - User
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','user5','user5','','','user5',0,'s','','user5@email.com','user5@email.com','user5','user5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3ac712c6-1ee3-4053-99b5-37949b3054c3','user4','user4','','','user4',0,'s','','user4@email.com','user4@email.com','user4','user4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3964ec47-2ac7-4c33-9660-523fa9ebb799','user3','user3','','','user3',0,'s','','user3@email.com','user3@email.com','user3','user3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3c84bf0a-b4f9-4059-99c0-669f649efefe','user1','user1','','','user1',0,'s','','user1@email.com','user1@email.com','user1','user1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3b972aa7-04f9-4db9-90da-e0a878a5fc7a','user2','user2','','','user2',0,'s','','user2@email.com','user2@email.com','user2','user2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )
-- for Internal - Common - Admin
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','admin5','admin5','','','admin5',0,'s','','admin5@email.com','admin5@email.com','admin5','admin5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4ac712c6-1ee3-4053-99b5-37949b3054c3','admin4','admin4','','','admin4',0,'s','','admin4@email.com','admin4@email.com','admin4','admin4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4964ec47-2ac7-4c33-9660-523fa9ebb799','admin3','admin3','','','admin3',0,'s','','admin3@email.com','admin3@email.com','admin3','admin3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4c84bf0a-b4f9-4059-99c0-669f649efefe','admin1','admin1','','','admin1',0,'s','','admin1@email.com','admin1@email.com','admin1','admin1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4b972aa7-04f9-4db9-90da-e0a878a5fc7a','admin2','admin2','','','admin2',0,'s','','admin2@email.com','admin2@email.com','admin2','admin2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )

--Insert Script for 5 rows of table: UsersInRoles
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1ac712c6-1ee3-4053-99b5-37949b3054c3',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1964ec47-2ac7-4c33-9660-523fa9ebb799',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1c84bf0a-b4f9-4059-99c0-669f649efefe',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1b972aa7-04f9-4db9-90da-e0a878a5fc7a',1 )

INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2ac712c6-1ee3-4053-99b5-37949b3054c3',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2964ec47-2ac7-4c33-9660-523fa9ebb799',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2c84bf0a-b4f9-4059-99c0-669f649efefe',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2b972aa7-04f9-4db9-90da-e0a878a5fc7a',2 )

INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3ac712c6-1ee3-4053-99b5-37949b3054c3',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3964ec47-2ac7-4c33-9660-523fa9ebb799',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3c84bf0a-b4f9-4059-99c0-669f649efefe',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3b972aa7-04f9-4db9-90da-e0a878a5fc7a',3 )

INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4ac712c6-1ee3-4053-99b5-37949b3054c3',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4964ec47-2ac7-4c33-9660-523fa9ebb799',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4c84bf0a-b4f9-4059-99c0-669f649efefe',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4b972aa7-04f9-4db9-90da-e0a878a5fc7a',4 )

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserSetting]') AND type in (N'U'))
DROP TABLE [dbo].[UserSetting]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Setting]') AND type in (N'U'))
DROP TABLE [dbo].[Setting]
GO

CREATE TABLE [dbo].[Setting]
(
	SettingId int identity(1,1) NOT NULL
		constraint PK_Setting primary key,
	[SettingName] nvarchar(64) NOT NULL
		constraint UIX_SettingName unique,
	[TypeName] [nvarchar] (128) NOT NULL,
	[Data] [varbinary] (max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[UserSetting]
(
	UserGuid uniqueidentifier NOT NULL,
	SettingId int NOT NULL
		constraint FK_UserSetting_Setting foreign key references Setting (SettingId),
	Data [varbinary] (max) NOT NULL,
	constraint PK_UserSetting primary key (UserGuid, SettingId)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MultitagContentView]'))
DROP VIEW [dbo].[MultitagContentView]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContentTags]') AND type in (N'U'))
DROP TABLE [dbo].[ContentTags]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MultitagContent]') AND type in (N'U'))
DROP TABLE [dbo].[MultitagContent]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Multitag]') AND type in (N'U'))
DROP TABLE [dbo].[Multitag]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tag]') AND type in (N'U'))
DROP TABLE [dbo].[Tag]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CategoryContent]') AND type in (N'U'))
DROP TABLE [dbo].[CategoryContent]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND type in (N'U'))
DROP TABLE [dbo].[Category]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Searcher]') AND type in (N'U'))
DROP TABLE [dbo].[Searcher]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Content]') AND type in (N'U'))
DROP TABLE [dbo].[Content]
GO

-- �����������ĳ����¼��ʶ��һ������������ʾһ�������ķ�Χ��
CREATE TABLE [dbo].[Searcher]
(
	[SearcherId] [int] NOT NULL
		constraint PK_Searcher primary key,
	[RegisterBy] [nvarchar](256) NOT NULL,
	[RegisterTime] [datetime] NOT NULL
)
CREATE TABLE [dbo].[Category]
(
	[CategoryId] [int] NOT NULL
		constraint PK_Category primary key,
	[SearcherId] [int] NOT NULL
		constraint FK_Category_Search foreign key references Searcher(SearcherId) ,
	[ParentId] [int] NOT NULL, -- 0: root
	[IdPath] [nvarchar](256) NOT NULL, --���ڼ���������Ŀ¼�е����ݡ�
	[Title] [nvarchar](128) NOT NULL,
	[Description] [nvarchar](256) NULL,
	[ContentAmount] [int] NOT NULL,
	[CachedContentIdsBinary] [varbinary](2000) NULL,
)
CREATE TABLE [dbo].[CategoryContent]
(
	[CategoryId] [int] NOT NULL
		constraint FK_CategoryContent_Category foreign key references Category(CategoryId),
	[ContentId] [int] NOT NULL,
	constraint PK_CategoryContent primary key( CategoryId, ContentId)
)
CREATE TABLE [dbo].[Tag]
(
	[TagId] [int] NOT NULL
		constraint PK_Tag primary key,
	[SearcherId] [int] NOT NULL
		constraint FK_Tag_Searcher foreign key references Searcher(SearcherId),
	[TagName] [nvarchar](64) NOT NULL,
	[Description] [nvarchar](256) NULL,
	constraint UIX_SearcherId_TagName unique (SearcherId, TagName)
)
CREATE TABLE [dbo].[Multitag]
(
	[MultitagId] [int] NOT NULL
		constraint PK_Multitag primary key,
	[SearcherId] [int] NOT NULL
		constraint FK_Mulitag_Searcher foreign key references Searcher(SearcherId),
	[TagNumber] [int] NOT NULL,
	[TagId0] [int] NOT NULL,
	[TagId1] [int] NOT NULL,
	[TagId2] [int] NOT NULL,
	[TagId3] [int] NOT NULL,
	[TagId4] [int] NOT NULL,
	[AccessCounter] [int] NOT NULL,
	[LastAccessTime] [datetime] NOT NULL,
	[ContentAmount] [int] NOT NULL,
	[CachedContentIdsBinary] [varbinary](2000) NULL,
	[CachedSubTags] [varbinary](max) NULL,
	[CacheIsOld] [bit] NOT NULL,
	constraint UIX_TagId01234 unique (TagId0, TagId1, TagId2, TagId3, TagId4)
)
CREATE TABLE [dbo].[MultitagContent]
(
	[MultitagId] [int] NOT NULL
		constraint FK_MultitagContent_Multitag foreign key references Multitag(MultitagId),
	[ContentId] [int] NOT NULL,
	constraint PK_MultitagContent primary key (MultitagId, ContentId)
)
CREATE TABLE [dbo].[ContentTags]
(
	[SearcherId] [int] NOT NULL
		constraint FK_ContentTags_Searcher foreign key references Searcher( SearcherId),
	[ContentId] [int] NOT NULL,
	[TagIdsBinary] [varbinary](20) NOT NULL,
	[TagCount] [int] NOT NULL,
	constraint PK_ContentTags primary key (SearcherId, ContentId)
)
GO

-- ���ݱ�
CREATE TABLE [dbo].[Content]
(
	[ContentId] [int] IDENTITY(1,1) NOT NULL
		constraint PK_Content primary key,
	[FolderCode] [char](14) NOT NULL -- ������ļ��еı��롣
		constraint UIX_FolderCode unique,
	[Title] [nvarchar](32) NOT NULL, -- ���ݵı���
	[FormatType] [nvarchar](50) NOT NULL, -- ���ݵĸ�ʽ���ͣ��������ļ�����֯�淶����ϵͳ���塣
	[CustomType] [nvarchar](128) NOT NULL, -- �Զ����ļ������ͣ���ʹ������д��������Ҫ���ַ�����
	[OwnerGuid] [uniqueidentifier] NOT NULL, -- ������
	[CreaterGuid] [uniqueidentifier] NOT NULL, -- ������
	[CreateDate] [datetime] NOT NULL, -- ��������
	[Path] [nvarchar](512) NOT NULL, -- �����·��������ڴ洢�ĸ�Ŀ¼�����·����
	[Description] [nvarchar](512) NULL,
	[EditCount] [int] NOT NULL, -- �༭����
	[EditLog] [nvarchar](max) NULL, -- �༭��¼
	[EditDate] [datetime] NOT NULL, -- ���һ�α༭ʱ��
	[IsDraft] [bit] NOT NULL, -- �Ƿ�ݸ�
	[PostCount] [int] NOT NULL, -- �ظ���������ʾ���������������ȡ��ظ�ʱ�����ڡ������ȡ�һ��㡱��ѡ��һ����PostCount��ȥGoodCount��BadCount����֪���ظ��˵�û����ȷ֧�ֺͷ��Ե�Ʊ����
	[GoodCount] [int] NOT NULL, -- ���Ĵ���
	[BadCount] [int] NOT NULL, -- �ȵĴ���
	[ViewCount] [int] NOT NULL, -- �������
	[FavoriteCount] [int] NOT NULL,  -- �ղش�������ʾ���ء��ŵ��ղؼС��Ƽ��ȵĴ�����
	[RecentViewCount] [int] NOT NULL, -- ����������
	[RecentPavoriteCount] [int] NOT NULL, --����ղصĴ���
	[RecentCountTime] [datetime] NOT NULL, -- ��ǰ���������ļ���ʱ�䡣
	[ContentSize] [int] NOT NULL, -- ���ݵ�ռ�ÿռ��С
	[Text] [nvarchar](max) NULL, -- ���ݵ��ı���ʽ����ȫ�ļ���ʹ��
	[Brief] [nvarchar](max) NULL, -- ���ݵ�ժҪ
	[Keywords] [nvarchar](max) NULL, -- �ؼ���
	[Rank] [int] NOT NULL, -- Ȩֵ
	[ApprovedDate] DateTime NULL, -- ����գ���ʾ��δ��� 
	[AllowPublish] bit NULL -- ��˽�����Ƿ���Թ����������δ���ʱΪ�ա� 
)
GO

CREATE VIEW dbo.MultitagContentView
AS
SELECT	dbo.Multitag.*, dbo.[Content].*
FROM	dbo.[Content] 
			INNER JOIN dbo.MultitagContent 
				ON dbo.[Content].ContentId = dbo.MultitagContent.ContentId 
			INNER JOIN dbo.Multitag 
				ON dbo.MultitagContent.MultitagId = dbo.Multitag.MultitagId

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SiteWare]') AND type in (N'U'))
DROP TABLE [dbo].[SiteWare]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Domain]') AND type in (N'U'))
DROP TABLE [dbo].[Domain]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Site]') AND type in (N'U'))
DROP TABLE [dbo].[Site]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileStorage]') AND type in (N'U'))
DROP TABLE [dbo].[FileStorage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Database]') AND type in (N'U'))
DROP TABLE [dbo].[Database]
GO

/*
	�����������ݿ��������ʵ������Щ���ݿ��������ʵ���䵱���ݿ��������
	ͨ��DbType/Instance/Connection���ڼ������ݿ����ƣ������Զ��������ݿ�������ַ�����
*/
CREATE TABLE [dbo].[Database]
(
	DatabaseId int identity(1,1) NOT NULL
		constraint PK_Database Primary key, 
	[DbType] nvarchar(32) NOT NULL,			-- ָ����ͬ���ݿ����ϵͳ����MSSQL2005��MSSQL2008��Sqlite��MySql�ȡ�
	Instance nvarchar(32) NULL,				-- ���ݿ�ʵ�������ƣ�����DbType����Ҫ��ʹ�á�
	[Connection] nvarchar(128) NULL,		-- ֧��������ӵ����ݿ�ʵ�����京�����DbType�����͡�
	AccessType nvarchar(32) NOT NULL		-- �����ݿ���MiniAccess�ж�Ӧ�����ݿ��ṩ�����͡�
)
GO

/*
	�ṩ�ļ��洢��λ��˵����
	Ŀǰ��֧�ֱ��ش洢�����洢λ���Ծ���·����������ʾλ��WebServer�µ�һ���ļ��С�
*/
CREATE TABLE [dbo].[FileStorage]
(
	FileStorageId int identity(1,1) NOT NULL
		constraint PK_FileStorage Primary key, 
	[StorageType] nvarchar(32) NOT NULL,	-- �洢���ͣ�ȡֵLocal��Share�ȡ�
	Location nvarchar(128) NOT NULL,			-- �ṩӲ�̴洢��λ�ã��京�����StorageType�����͡�
)
GO

/*
	�����վ��ע�����ݣ�ÿ����¼����һ��ע�����վ��
*/
CREATE TABLE [dbo].[Site]
(
	SiteGuid uniqueidentifier NOT NULL
		constraint PK_Site primary key, 
	SiteName nvarchar(64) NOT NULL
		constraint UIX_SiteName unique,
	Title nvarchar(128) NOT NULL,
	[Description] nvarchar(512) NULL,

	[OwnerGuid] uniqueidentifier NOT NULL,	-- ��վ�������ߣ���������վ���õ�Ruandao�˺š�
	UserLevel nvarchar(16) NOT NULL,		-- ��վ�ķ��񼶱����Free����ͨ�շ��û�Normal���߼��շ��û�Advance�������û�CustomUser�ȵȡ�
	CreateDate datetime NOT NULL,			-- ������վ������
	
	DatabaseId int NOT NULL			    	-- ��վ�µ�������ʹ�õ�Ĭ�����ݿ�ʵ���������������ʵ����
		constraint FK_Site_Database foreign key references [Database](DatabaseId),
	SiteDbName nvarchar(64) NOT NULL,		-- ��վ�����ݿ�����ƣ�������Ϊ�ա�
	FileStorageId int NOT NULL				-- ��վ�µ�������������ļ��ķ�����λ�á�
		constraint FK_Site_FileStorage foreign key references [FileStorage](FileStorageId),
	ContainsAccount int NOT NULL,			-- ��վ���ݿ����Ƿ�����ʺ����ݱ��������������վ�µ�����������ʹ�ø��ʺ����ݱ��
);
GO

/*
	�����뵽IIS�е�ʱ������ͷ�е������������������ض�����վ��������Ҫ������Ӧ���ض���վ
	����������Ҫ�����ע�ᡣ������������������Ӧ������
*/
CREATE TABLE [dbo].[Domain]
(
	DomainId int Identity(1,1) NOT NULL
		constraint PK_Domin primary key, 
	DomainName nvarchar(64) NOT NULL			-- ������������ʽ����abc.ruandao.com�ȡ�
		constraint UIX_SiteDomainName unique,
	SiteGuid uniqueidentifier NOT NULL
		constraint FK_Domain_Site foreign key references [Site](SiteGuid),
	DomainType nvarchar(64) NOT NULL,			-- ͬһ����վ����������ָ�����վ�����վ�������վ�ĸ�������������ʾ��������bbs����ʾ��̳��վ��������ͬһ��SiteGuid��DomainType���ܻ��ظ�������һ���г���վ�µĶ������վ�㣬�ֱ���һ��������վ�µĶ�����͸���վ�㡣
	SpecialPath nvarchar(64) NULL				-- ר������վ��������ȡֵ����վӦ�õĲ���·����������Shop, ��ʾ����������������ʱ����дΪhttp://domain/Shop/...
)
GO

/*
	��ͬ��վ�е�����ʵ����
*/
CREATE TABLE [dbo].[SiteWare]
(
	SiteWareId int identity(1,1) NOT NULL
		constraint PK_SiteWare primary key,
	SiteGuid uniqueidentifier NOT NULL
		constraint FK_SiteWare_Site foreign key references [Site]( SiteGuid ), 
	WareName nvarchar(64) NOT NULL,
	constraint UIX_SiteGuid_WareName unique (SiteGuid, WareName),

	CreateDate datetime NOT NULL,
	
	DatabaseId int NULL						-- Ϊ�ձ�ʾʹ����վ�����ݿ�ʵ����
		constraint FK_SiteWare_Database foreign key references [Database]( DatabaseId ), 
	WareDbName nvarchar(64) NULL,			-- Ϊ�ձ�ʾ����������ʹ��������վ�����ݿ⣨����������ǰ�������Ǳ���û�г�ͻ����ȡֵ"<<default>>"��ʾʹ��Ĭ�ϵ����ݿ����֣���: <SiteName>_<WareName>, Ϊ����ֵ��ʾһ�����ݿ����ơ�
	
	FileStorageId int NULL					-- Ϊ�ձ�ʾʹ����վ���ļ��洢λ�ã�����վ�����ļ��еĸ��ļ��С�
		constraint FK_SiteWare_FileStorage foreign key references FileStorage( FileStorageId ), 
	WareFolder nvarchar(64) NULL,			-- Ϊ�ձ�ʾ������������վ���ļ����λ�ã���������ǰ���������ļ������ļ�������û�г�ͻ��,��<FileStorageId Path>/<SiteName>��ȡֵ"<<default>>"��ʾʹ��Ĭ�ϵ��ļ����λ�ã���: <FileStorageId Path>/<SiteName>/<WareName>, �������վĿ¼�µ�������Ŀ¼�£�����ֵ��ʾ�ض����ļ�λ��<FileStorageId Path>/<SiteName>/<WareFolder>��
	
	-- �������ʺ����ݾ��������վ�������ݿ��С�
	AccountContextCode nvarchar(16) NOT NULL,	-- �˴�����ʹ���������Ϊ����ȷ����Ҫ���õ����ݿ⡣
	RoleCategory nvarchar(64) NOT NULL,			-- �˴�����ʹ�������ԭ��ͬ�ϡ�

	Allow bit NOT NULL,							-- ����վ�н��ø�������
	LockDomain nvarchar(50) NULL 	    		-- ��������������������ǿգ�ȡֵ�������������ʾ��������ʵ��ֻ���ɸ�ָ�����������������������������������������Ӧ��
)
GO






GO
